/*
 * @Author: LiDongxue
 * @Date: 2018-05-24 10:56:54
 * @Last Modified by: lidongxue
 * @Last Modified time: 2018-07-07 18:05:17
 */

const paymentTools = {
    setAllPerformance, //  业绩比例表
    saleBonusDisUpdate, //销售分红提成
    consumeBonusDisUpdate, // 消耗分红提成
    serviceBonusDisUpdate // 服务分红提成
};

/**
 * 根据订单 TN_ACHIEVEMENT_SCALE（业绩比例表）id 更新关联订单
 *
 * target_id => 项目/商品/美丽金/会员卡ID
 * sale_dis 收款额比例
 * consume_dis 消耗比例
 * service_dis  服务提成点
 * mer_admin_id
 * merchant_id
 * tp 1项目:2商品:3美丽金:4会员卡
 *  BIG_CATEGORY 大类
 *  SMALL_CATEGORY 小类       =》美丽金和会员卡 没有大类 小类
 *  gift_dis 赠品比例
 *  active_dis 活动比例点
 *  share_dis 分享收益比例
 *  service_performance_dis 服务比例
 *
 * 收款业绩 = 实收款 * 收款业绩比例 《实际收款只存在大表中》
 * 消耗业绩 = 实收款 * 消耗业绩比例 《实际收款只存在小表中》
 * 服务业绩 = 实收款 * 服务业绩比例 《实际收款只存在小表中》
 * 赠送||特价项目 服务业绩 = 原价 * 配置比例 * 服务业绩比例
 * tn_customer_order.service_performance
 * tn_customer_order.consume_performance
 * tn_customer_order_all.sale_performance
 *
 * 参数：TN_ACHIEVEMENT_SCALE.id ，connection
 * 返回值： json {code:number,msg:string }
 */
const utility = require('./validationFun'); // 参数验证函数

async function setAllPerformance(id, connection) {

    if (!utility.isNumber(id)) { // 不是id不是数字 直接返回
        return {code: 201, msg: 'id类型错误！'};
    }
    if (!connection) { // 没有传递 connection
        return {code: 201, msg: '没有传入数据库连接！'};
    }
    // service_dis  服务月度 分红提点 算分红用的
    // SERVICE_PERFORMANCE_DIS 服务业绩提点  算业绩用的
    console.log('搜索具体参数！！！');
    const achievementsql = `select 
             target_id "target_id",
             sale_dis "sale_dis",
             consume_dis "consume_dis",
             service_dis "service_dis",
             mer_admin_id "mer_admin_id",
             merchant_id "merchant_id",
             tp "tp",
             big_category "big_category",
             small_category "small_category",
             gift_dis "gift_dis",
             active_dis "active_dis",
             share_dis "share_dis",
             service_performance_dis "service_performance_dis"
             from TN_ACHIEVEMENT_SCALE where id = ${id}`;

    // 获得修改的配置
    const result = await connection.execute(achievementsql, []);
    if (result.rows.length === 0) {
        return {code: 201, msg: '没有查到相关的配置表！'};
    }
    // tp 项目类型
    const achievement = result.rows[0];
    if (!achievement.mer_admin_id) {
        return {code: 201, msg: '缺少管理中心！'};
    }
    const tp = achievement.tp;
    let updatesql = ``;
    switch (tp) {
        case 1://项目         小表：管理中心，商家，项目id       =》大类 小类 在品相表中
            // target_id => small_category => big_category => merchant_id => mer_admin_id
            // tn_customer_order_alls.discount_type is"优惠类型 1普通 2身份价 3活动价 4会员价 5次卡价"
            // 存在具体项目 => 大表修改收款 小表 修改 消耗 服务 活动 特价
            if (achievement.target_id) {
                // 1.修改大表                                                                                 普通
                if (achievement.merchant_id) { // 对店铺   => 收款业绩比例     收款业绩  收款分红比例<员工设置>
                    console.log('具体项目，具体门店！！！');
                    let bigsql =    `update tn_customer_order_all set
                                    compute_sale_dis = ${achievement.sale_dis},
                                    compute_service_dis = ${achievement.service_dis},
                                    sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                    recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                                    where  project_id = ${achievement.target_id}
                                    and order_type  in (1,2)
                                    and settle_status = 0
                                    and merchant_id = ${achievement.merchant_id}`;

                    // order_type <> 4 不是赠品 3订单转赠 4 赠品 5 次卡绑定项目  1正常 2 特价订单
                    await connection.execute(bigsql, []);
                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // 此处应该判断订单类型
                    let smallsql1 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.service_performance_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

         
                                where

                                and settle_status = 0
                                and order_state = 4
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                on t.order_id = alls.id where alls.order_type = 1 and t.merchant_id = ${achievement.merchant_id} 
                                and t.project_id = ${achievement.target_id};`;

                    let smallsql2 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.active_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                where 
                                and settle_status = 0
                                and order_state = 4
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                on t.order_id = alls.id where  alls.order_type = 2 and t.merchant_id = ${achievement.merchant_id} 
                                and t.project_id = ${achievement.target_id} );`;

                    // order_type 特价订单
                    let smallsql4 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.gift_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = 0,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = 0

                                where
                                and settle_status = 0
                                and order_state = 4
                                and order_id in (select id from tn_customer_order_all  t where t.order_type = 4  
                                and t.merchant_id = ${achievement.merchant_id} 
                                and t.project_id = ${achievement.target_id});`;
                    let smallsql = `begin  ${smallsql1} ${smallsql2} ${smallsql4} end;`;
                    await connection.execute(smallsql, []);

                    return {code: 200, msg: '具体服务，有门店，操作成功！'};
                } else { // 对管理中心  需要排除诸多的影响
                    console.log('具体项目，管理中心！！！');
                    let bigsql = `update tn_customer_order_all set
                                compute_sale_dis = ${achievement.sale_dis},
                                compute_service_dis = ${achievement.service_dis},
                                sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                                where mer_admin_id = ${achievement.mer_admin_id}
                                and project_id = ${achievement.target_id}
                                and settle_status = 0
                                
                                and merchant_id not in (select merchant_id from tn_achievement_scale 
                                where status =1 and tp =1  and small_category is null and big_category is null
                                and  mer_admin_id = ${achievement.mer_admin_id}
                                and merchant_id is not null )
                                 
                                and order_type  in (1,2)`;
                    await connection.execute(bigsql, []);

                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // CONSUME_AMOUNT 消耗金额 暂时留存
                    // SERVICE_AMOUNT 服务金额 暂时留存
                    // 缺少一个单价字段 取大表 在小表中建立

                    let smallsql1245 = `update tn_customer_order set
                                        compute_service_dis = ${achievement.service_performance_dis},
                                        compute_consume_dis = ${achievement.consume_dis},
                                        consume_performance = project_price * ${achievement.consume_dis} / 100,
                                        service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                        serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                        recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000 
                
                                        where settle_status = 0
                                        and order_state = 4
                                        
                                        and merchant_id not in (select merchant_id from tn_achievement_scale 
                                        where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                        and target_id is null
                                        and  mer_admin_id = ${achievement.mer_admin_id})

                                        and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                        on t.order_id = alls.id where alls.order_type =  1  
                                        t.mer_admin_id = ${achievement.mer_admin_id}
                                        and t.project_id = ${achievement.target_id});`;
                    let smallsql3 = `update tn_customer_order tn set
                                    compute_service_dis = ${achievement.active_dis},
                                    compute_consume_dis = ${achievement.consume_dis},
                                    consume_performance = project_price * ${achievement.consume_dis} / 100,
                                    service_performance = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.active_dis} /100,
                                    serv_div = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                    recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
            
                                    where 
                                    settle_status = 0
                                    and order_state = 4
                                    
                                    and merchant_id not in (select merchant_id from tn_achievement_scale 
                                    where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                    and target_id is null
                                    and  mer_admin_id = ${achievement.mer_admin_id})
    
                                    and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                        on t.order_id = alls.id where  alls.order_type = 2 
                                        and t.mer_admin_id = ${achievement.mer_admin_id}
                                        and t.project_id = ${achievement.target_id}
                                    );`;
                    let smallsql6 = `update tn_customer_order tn set
                                        compute_service_dis = ${achievement.gift_dis},
                                        compute_consume_dis = ${achievement.consume_dis},
                                        consume_performance = project_price * ${achievement.consume_dis} / 100,
                                        service_performance = (select proj_old_prise  from tn_customer_order_all t
                                            where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                                        serv_div = (select proj_old_prise  from tn_customer_order_all t
                                            where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                        recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                
                                        where 
                                        settle_status = 0
                                        and order_state = 4
                                        
                                        and merchant_id not in (select merchant_id from tn_achievement_scale 
                                        where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                        and target_id is null
                                        and  mer_admin_id = ${achievement.mer_admin_id})
        

                                        and order_id in (select id from tn_customer_order_all  t where t.order_type = 4 
                                            and t.project_id = ${achievement.target_id}
                                            and t.mer_admin_id = ${achievement.mer_admin_id});`;
                    let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                    await connection.execute(smallsql, []);
                    return {code: 200, msg: '具体服务，无门店，操作成功！'};
                }
            }
            if (achievement.small_category) { // 如果存在具体项目id 则不需要小类  关联一批项目
                // bigsql 处理大表
                // smallsql 处理小表
                if (achievement.merchant_id) {
                    console.log('具体门店，小类！！！');
                    let bigsql = `update tn_customer_order_all set
                              compute_sale_dis = ${achievement.sale_dis},
                              compute_service_dis = ${achievement.service_dis},
                              sale_performance = total_amount * ${achievement.sale_dis} / 100,
                              recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                              where  settle_status = 0
                              and project_id  in (select id from t_project_cfg where body_part =
                              ${achievement.small_category}) 
                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                            tp = 1 and target_id is not null  and merchant_id = ${achievement.merchant_id})
                            and merchant_id = ${achievement.merchant_id}
                            and order_type  in (1,2)`;
                    await connection.execute(bigsql, []);

                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // CONSUME_AMOUNT 消耗金额 暂时留存
                    // SERVICE_AMOUNT 服务金额 暂时留存
                    // 缺少一个单价字段 取大表 在小表中建立

                    let smallsql1245 = `update tn_customer_order set
                                compute_service_dis = ${achievement.service_performance_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where merchant_id = ${achievement.merchant_id} 
                                and settle_status = 0
                                and order_state = 4
                                and project_id  in (select id from t_project_cfg where body_part = ${achievement.small_category}  ) 
                    
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null and merchant_id = ${achievement.merchant_id})
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                on t.order_id = alls.id where alls.order_type = 1);`;
                    let smallsql3 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.active_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where merchant_id = ${achievement.merchant_id} 
                                and settle_status = 0
                                and order_state = 4
                                
                                and project_id  in (select id from t_project_cfg where body_part =
                                ${achievement.small_category}) 
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null 
                                and merchant_id = ${achievement.merchant_id} )
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                    on t.order_id = alls.id where  alls.order_type = 2 );`;

                    let smallsql6 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.gift_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where settle_status = 0 and order_state = 4
                                and project_id  in (select id from t_project_cfg where body_part =
                                 ${achievement.small_category}) 
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null 
                                and merchant_id = ${achievement.merchant_id} )
                                and order_id in (select id from tn_customer_order_all  t where t.order_type = 4 
                                    and t.merchant_id = ${achievement.merchant_id} );`;
                    let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                    await connection.execute(smallsql, []);

                    return {code: 200, msg: '小类，有门店，操作成功！'};
                } else {
                    console.log('没有具体门店，小类！！！');
                    let bigsql = `update tn_customer_order_all set
                                        compute_sale_dis = ${achievement.sale_dis},
                                        compute_service_dis = ${achievement.service_dis},
                                        sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                        recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000

                                        where mer_admin_id = ${achievement.mer_admin_id}
                                        and settle_status = 0

                                        and project_id  in (select id from t_project_cfg where body_part =
                                        ${achievement.small_category} )

                                        and merchant_id not in (select merchant_id from tn_achievement_scale 
                                        where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                        and  mer_admin_id = ${achievement.mer_admin_id} and target_id is null  )

                                                
                                        and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                        tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                                        and merchant_id is  null )
                                        and order_type  in (1,2)`;
                    await connection.execute(bigsql, []);

                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // CONSUME_AMOUNT 消耗金额 暂时留存
                    // SERVICE_AMOUNT 服务金额 暂时留存
                    // 缺少一个单价字段 取大表 在小表中建立

                    let smallsql1245 = `update tn_customer_order set
                                            compute_service_dis = ${achievement.service_performance_dis},
                                            compute_consume_dis = ${achievement.consume_dis},
                                            consume_performance = project_price * ${achievement.consume_dis} / 100,
                                            service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                            serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                            recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                            where
                                            mer_admin_id = ${achievement.mer_admin_id}
                                            and settle_status = 0
                                            and order_state = 4

                                            and project_id  in (select id from t_project_cfg where body_part =
                                            ${achievement.small_category}  ) 
                                            and merchant_id not in (select merchant_id from tn_achievement_scale 
                                            where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                            and  mer_admin_id = ${achievement.mer_admin_id} and target_id is null  )
                                                    
                                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                            tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                                            and merchant_id is  null )
                                            and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                            on t.order_id = alls.id where alls.order_type = 1 );`;
                    let smallsql3 = `update tn_customer_order tn set
                                            compute_service_dis = ${achievement.active_dis},
                                            compute_consume_dis = ${achievement.consume_dis},
                                            consume_performance = project_price * ${achievement.consume_dis} / 100,
                                            service_performance = (select proj_old_prise  from tn_customer_order_all t
                                                where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                            serv_div = (select proj_old_prise  from tn_customer_order_all t
                                                where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                            recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                            where
                                            mer_admin_id = ${achievement.mer_admin_id}
                                            and settle_status = 0
                                            and order_state = 4

                                            and project_id  in (select id from t_project_cfg where body_part  =
                                            ${achievement.small_category}) 
                                            and merchant_id not in (select merchant_id from tn_achievement_scale 
                                            where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                            and  mer_admin_id = ${achievement.mer_admin_id} and target_id is null  )
                                                   
                                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                            tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                                            and merchant_id is  null )
                                            and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                                on t.order_id = alls.id where  alls.order_type = 2 );`;
                    let smallsql6 = `update tn_customer_order tn set
                                            compute_service_dis = ${achievement.gift_dis},
                                            compute_consume_dis = ${achievement.consume_dis},
                                            consume_performance = project_price * ${achievement.consume_dis} / 100,
                                            service_performance = (select proj_old_prise  from tn_customer_order_all t
                                                where t.id = tn.order_id ) * ${achievement.gift_dis} /100,
                                            serv_div = (select proj_old_prise  from tn_customer_order_all t
                                                where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                            recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                            where settle_status = 0 and order_state = 4
                                            and project_id  in (select id from t_project_cfg where body_part =
                                            ${achievement.small_category}) 
                                            and merchant_id not in (select merchant_id from tn_achievement_scale 
                                            where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                            and  mer_admin_id = ${achievement.mer_admin_id} and target_id is null  )



                                                    
                                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                            tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                                            and merchant_id is  null)
                                            and order_id in (select id from tn_customer_order_all  t where t.order_type = 4
                                            t.mer_admin_id = ${achievement.mer_admin_id}
                                            );`;
                    let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                    await connection.execute(smallsql, []);

                    return {code: 200, msg: '大类，无门店，操作成功！'};

                }
            }
            if (achievement.big_category) { // 如果存在具体项目id  则 不需要大类  关联一批项目
                // bigsql  处理大表
                // smallsql 处理小表
                if (achievement.merchant_id) {
                    console.log('具体门店，大类！！！');
                    let bigsql = `update tn_customer_order_all set
                                        compute_sale_dis = ${achievement.sale_dis},
                                        compute_service_dis = ${achievement.service_dis},
                                        sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                        recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                                        where merchant_id = ${achievement.merchant_id} 
                                        and settle_status = 0

                                        and project_id  in (select id from t_project_cfg where beaut_type  in 
                                        ${achievement.big_category} )
                                        and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                        tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                        and target_id is null  and merchant_id = ${achievement.merchant_id}))
                                        and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                        tp = 1 and target_id is not null 
                                        and merchant_id = ${achievement.merchant_id} )
                                        and order_type  in (1,2)`;
                                        
                    await connection.execute(bigsql, []);
                                
                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // CONSUME_AMOUNT 消耗金额 暂时留存
                    // SERVICE_AMOUNT 服务金额 暂时留存
                    // 缺少一个单价字段 取大表 在小表中建立

                    let smallsql1245 = `update tn_customer_order set
                                compute_service_dis = ${achievement.service_performance_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where merchant_id = ${achievement.merchant_id} 
                                and settle_status = 0
                                and order_state = 4
                                and project_id  in (select id from t_project_cfg where beaut_type = ${achievement.big_category} ) 
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null and merchant_id = ${achievement.merchant_id}))
                                        
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null and  merchant_id = ${achievement.merchant_id} )
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                on t.order_id = alls.id where alls.order_type = 1 );`;
                    let smallsql3 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.active_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where  settle_status = 0 and order_state = 4
                                
                                and project_id  in (select id from t_project_cfg where beaut_type =
                                ${achievement.big_category} ) 
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null  and merchant_id = ${achievement.merchant_id}))
                                        
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null 
                                and merchant_id = ${achievement.merchant_id} )
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                    on t.order_id = alls.id where  alls.order_type = 2
                                    t.merchant_id = ${achievement.merchant_id}   );`;
                    let smallsql6 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.gift_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis}/ 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
         
                                where merchant_id = ${achievement.merchant_id} 
                                and settle_status = 0
                                and order_state = 4
                                and project_id  in (select id from t_project_cfg where beaut_type  =
                                ${achievement.big_category}) 
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null  and mer_admin_id =
                                ${achievement.mer_admin_id} and merchant_id = ${achievement.merchant_id}))
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null 
                                and merchant_id = ${achievement.merchant_id} )
                                and order_id in (select id from tn_customer_order_all  t where t.order_type = 4 );`;

                    let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                    await connection.execute(smallsql, []);
                                
                    return {code: 200, msg: '大类，有门店，操作成功！'};
                } else {
                    console.log('管理中心，大类！！！');
                    let bigsql = `update tn_customer_order_all set
                                    compute_sale_dis = ${achievement.sale_dis},
                                    compute_service_dis = ${achievement.service_dis},
                                    sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                    recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000

                                    where mer_admin_id = ${achievement.mer_admin_id}
                                    and settle_status = 0
                                    and project_id  in (select id from t_project_cfg where beaut_type = 
                                        ${achievement.big_category} ) 
                                    and merchant_id not in (select merchant_id from tn_achievement_scale 
                                    where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                    and  mer_admin_id = ${achievement.mer_admin_id} and target_id is null  )

                                    and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                    tn_achievement_scale where status =1 and tp = 1 and small_category is not null and mer_admin_id =
                                    ${achievement.mer_admin_id} and target_id is null) and merchant_id is null)
                                    and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                    tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                                    and merchant_id is  null)
                                    and order_type  in (1,2)`;
                    await connection.execute(bigsql, []);

                    // settle_status  1已结算 0 未结算
                    // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                    // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                    // CONSUME_AMOUNT 消耗金额 暂时留存
                    // SERVICE_AMOUNT 服务金额 暂时留存
                    // 缺少一个单价字段 取大表 在小表中建立

                    let smallsql1245 = `update tn_customer_order set
                                        compute_service_dis = ${achievement.service_performance_dis},
                                        compute_consume_dis = ${achievement.consume_dis},
                                        consume_performance = project_price * ${achievement.consume_dis} / 100,
                                        service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                        serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                        recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                        where
                                        mer_admin_id = ${achievement.mer_admin_id}
                                        and settle_status = 0
                                        and order_state = 4

                                        and merchant_id not in (select merchant_id from tn_achievement_scale 
                                        where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                        and   target_id is null )
                                
                                        and project_id  in (select id from t_project_cfg where beaut_type  in 
                                        ${achievement.big_category} ) 
                                        and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                        tn_achievement_scale where status =1 and tp = 1 and small_category is not null and mer_admin_id =
                                        ${achievement.mer_admin_id} and target_id is null  and merchant_id is  null))
                                                
                                        and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                        tp = 1 and target_id is not null and merchant_id = ${achievement.merchant_id} 
                                        and merchant_id is  null)
                                        and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                        on t.order_id = alls.id where alls.order_type = 1 );`;
                                        
                    let smallsql3 = `update tn_customer_order tn set
                                    compute_service_dis = ${achievement.active_dis},
                                    compute_consume_dis = ${achievement.consume_dis},
                                    consume_performance = project_price * ${achievement.consume_dis} / 100,
                                    service_performance = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                    serv_div = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                    recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                    where  settle_status = 0 and order_state = 4
                                    and merchant_id not in (select merchant_id from tn_achievement_scale 
                                    where status =1 and tp =1 and merchant_id is not null and small_category is null 
                                    and big_category is null and target_id is not null )
                            
                                    and project_id  in (select id from t_project_cfg where beaut_type =
                                    ${achievement.big_category}  ) 
                                    and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                    tn_achievement_scale where status =1 and tp = 1 and small_category is not null and mer_admin_id =
                                    ${achievement.mer_admin_id} and target_id is null and merchant_id is  null))
                                            
                                    and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                    tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id} 
                                    and merchant_id is  null)
                                    and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                    on t.order_id = alls.id where alls.discount_type = 6 and alls.order_type = 2 
                                    t.mer_admin_id = ${achievement.mer_admin_id}
                                    );`;

                    let smallsql6 = `update tn_customer_order tn  set
                                    compute_service_dis = ${achievement.gift_dis},
                                    compute_consume_dis = ${achievement.consume_dis},
                                    consume_performance = project_price * ${achievement.consume_dis} / 100,
                                    service_performance = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                                    serv_div = (select proj_old_prise  from tn_customer_order_all t
                                        where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                    recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000

                                    where
                                    mer_admin_id = ${achievement.mer_admin_id}
                                    and settle_status = 0
                                    and order_state = 4

                                    and merchant_id not in (select merchant_id from tn_achievement_scale 
                                    where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                    and   target_id is null  )
                            
                                    and project_id  in (select id from t_project_cfg where beaut_type =
                                    ${achievement.big_category}  ) 
                                        
                                    and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                    tn_achievement_scale where status =1 and tp = 1 and small_category is not null and mer_admin_id =
                                    ${achievement.mer_admin_id} and target_id is null and merchant_id is  null  ))
                                            
                                    and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                    tp = 1 and target_id is not null and mer_admin_id =
                                    ${achievement.mer_admin_id} and merchant_id is  null)
                                    and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                    on t.order_id = alls.id where alls.discount_type =6 );`;
                    
                    let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                    await connection.execute(smallsql, []);

                    return {code: 200, msg: '大类，无门店，操作成功！'};
                }
            }
            if (achievement.merchant_id) { //  店铺
                // bigsql  处理大表
                // smallsql  处理小表
                // 1.修改大表
                console.log('具体店铺！！！！');
                let bigsql = `update tn_customer_order_all set
                            compute_sale_dis = ${achievement.sale_dis},
                            compute_service_dis = ${achievement.service_dis},
                            sale_performance = total_amount * ${achievement.sale_dis} / 100,
                            recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                            where merchant_id = ${achievement.merchant_id} 
                            and mer_admin_id = ${achievement.mer_admin_id}
                            and settle_status = 0
                            and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                            from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                            and small_category is null and target_id is null  and mer_admin_id =
                            ${achievement.mer_admin_id} and merchant_id = ${achievement.merchant_id} )) 
        
                            and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                            tn_achievement_scale where status =1 and tp = 1 and small_category is not null  
                            and target_id is null and mer_admin_id =
                            ${achievement.mer_admin_id} and merchant_id = ${achievement.merchant_id}))
                                    
                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                            tp = 1 and target_id is not null and merchant_id = ${achievement.merchant_id} )
                            and merchant_id = ${achievement.merchant_id}
                            and order_type  in (1,2)`;

                await connection.execute(bigsql, []);
                                    
                // settle_status  1已结算 0 未结算
                // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                // CONSUME_AMOUNT 消耗金额 暂时留存
                // SERVICE_AMOUNT 服务金额 暂时留存
                // 缺少一个单价字段 取大表 在小表中建立

                let smallsql1245 = `update tn_customer_order set
                             compute_service_dis = ${achievement.service_performance_dis},
                             compute_consume_dis = ${achievement.consume_dis},
                             consume_performance = project_price * ${achievement.consume_dis} / 100,
                             service_performance = project_price * ${achievement.service_performance_dis} / 100,
                             serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                             recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                             where merchant_id = ${achievement.merchant_id} 
                             and settle_status = 0
                             and order_state = 4
                             and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                            from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                            and small_category is null  
                            and target_id is null
                            and merchant_id = ${achievement.merchant_id} )) 
        
                            and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                            tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                            and target_id is null
                            and merchant_id = ${achievement.merchant_id}))        
                            and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                            tp = 1 and target_id is not null and merchant_id = ${achievement.merchant_id})
                            and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                            on t.order_id = alls.id where alls.order_type = 1  );`;

                let smallsql3 = `update tn_customer_order tn set
                            compute_service_dis = ${achievement.active_dis},
                            compute_consume_dis = ${achievement.consume_dis},
                            consume_performance = project_price * ${achievement.consume_dis} / 100,
                            service_performance = (select proj_old_prise  from tn_customer_order_all t
                                where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                            serv_div = (select proj_old_prise  from tn_customer_order_all t
                                where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_performance_dis} / 10000,
                            recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                            where merchant_id = ${achievement.merchant_id} 
                            and mer_admin_id = ${achievement.mer_admin_id}
                            and settle_status = 0
                            and order_state = 4
                            and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                           from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                           and small_category is null 
                           and target_id is null
                           and mer_admin_id =
                           ${achievement.mer_admin_id} and merchant_id = ${achievement.merchant_id} )) 
       
                           and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                           tn_achievement_scale where status =1 and tp = 1 and small_category is not null  
                           and target_id is null
                           and merchant_id = ${achievement.merchant_id}))
                                   
                           and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                           tp = 1 and target_id is not null 
                           and merchant_id = ${achievement.merchant_id} )
                           and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                            on t.order_id = alls.id where  alls.order_type = 2 );`;

                let smallsql6 = `update tn_customer_order tn set
                           compute_service_dis = ${achievement.gift_dis},
                           compute_consume_dis = ${achievement.consume_dis},
                           consume_performance = project_price * ${achievement.consume_dis} / 100,
                           service_performance = (select proj_old_prise  from tn_customer_order_all t
                            where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                           serv_div = (select proj_old_prise  from tn_customer_order_all t
                            where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                           recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                           where merchant_id = ${achievement.merchant_id} 
                           and mer_admin_id = ${achievement.mer_admin_id}
                           and settle_status = 0
                           and order_state = 4
                           and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                          from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                          and target_id is null
                          and small_category is null 
                          and merchant_id = ${achievement.merchant_id} )) 
      
                          and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                          tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                          and target_id is null
                          and merchant_id = ${achievement.merchant_id}))
                                  
                          and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                          tp = 1 and target_id is not null 
                          and merchant_id = ${achievement.merchant_id})
                          and order_id in (select id from tn_customer_order_all  t where t.order_type = 4);`;

                let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                await connection.execute(smallsql, []);
      
                return {code: 200, msg: '更新成功！项目店铺'};
            }
            if (achievement.mer_admin_id) {   // 管理中心
                // bigsql  处理大表
                // smallsql  处理小表
                // 1.修改大表
                console.log('管理中心！！！');
                let bigsql = `update tn_customer_order_all set
                                compute_sale_dis = ${achievement.sale_dis},
                                compute_service_dis = ${achievement.service_dis},
                                sale_performance = total_amount * ${achievement.sale_dis} / 100,
                                recv_div = total_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
                                where mer_admin_id = ${achievement.mer_admin_id}
                                and settle_status = 0
                                
                                and merchant_id not in (select merchant_id from tn_achievement_scale 
                                where status =1 and tp =1 and merchant_id is not null and small_category is null and big_category is null
                                and target_id is null and  mer_admin_id = ${achievement.mer_admin_id})

                                and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                                from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                                and small_category is null  and target_id is null  and mer_admin_id =
                                ${achievement.mer_admin_id} )) 
            
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null and mer_admin_id = ${achievement.mer_admin_id}))
                                        
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id})
                                and order_type  in (1,2)`;
                await connection.execute(bigsql, []);

                // settle_status  1已结算 0 未结算
                // pay_state 支付状态 0 未支付 1 已支付 2已退款 3支付中
                // 服务 消耗   消耗金额 消耗比例 消耗业绩 消耗提成比例
                // CONSUME_AMOUNT 消耗金额 暂时留存
                // SERVICE_AMOUNT 服务金额 暂时留存
                // 缺少一个单价字段 取大表 在小表中建立

                let smallsql1245 = `update tn_customer_order set
                                 compute_service_dis = ${achievement.service_performance_dis},
                                 compute_consume_dis = ${achievement.consume_dis},
                                 consume_performance = project_price * ${achievement.consume_dis} / 100,
                                 service_performance = project_price * ${achievement.service_performance_dis} / 100,
                                 serv_div = project_price * ${achievement.service_dis} * ${achievement.service_performance_dis} / 10000,
                                 recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                                 where mer_admin_id = ${achievement.mer_admin_id}
                                 and settle_status = 0
                                 and order_state = 4
                                 
                                 and merchant_id not in (select merchant_id from tn_achievement_scale 
                                where status =1 and tp =1 and merchant_id is not null and small_category is null and 
                                big_category is null  and target_id is null and  mer_admin_id = ${achievement.mer_admin_id})

                                and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                                from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                                and target_id is null  and small_category is null and mer_admin_id =
                                ${achievement.mer_admin_id} )) 
            
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null  and mer_admin_id = ${achievement.mer_admin_id}))
                                        
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id})
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                on t.order_id = alls.id where alls.order_type = 1  );`;

                let smallsql3 = `update tn_customer_order tn set
                                 compute_service_dis = ${achievement.active_dis},
                                 compute_consume_dis = ${achievement.consume_dis},
                                 consume_performance = project_price * ${achievement.consume_dis} / 100,
                                 service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} / 100,
                                 serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.active_dis} * ${achievement.service_dis} / 10000,
                                 recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                                 where mer_admin_id = ${achievement.mer_admin_id}
                                 and settle_status = 0
                                 and order_state = 4
                                 
                                 and merchant_id not in (select merchant_id from tn_achievement_scale 
                                where status =1 and tp =1 and merchant_id is not null and small_category is null and 
                                big_category is null  and target_id is null and  mer_admin_id = ${achievement.mer_admin_id})

                                and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                                from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                                and target_id is null  and small_category is null and mer_admin_id =
                                ${achievement.mer_admin_id} )) 
            
                                and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                                tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                                and target_id is null  and mer_admin_id = ${achievement.mer_admin_id}))
                                        
                                and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                                tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id})
                                and order_id in (select t.order_id from tn_customer_order t left join  tn_customer_order_all alls
                                    on t.order_id = alls.id where  alls.order_type = 2 );`;

                let smallsql6 = `update tn_customer_order tn set
                                compute_service_dis = ${achievement.gift_dis},
                                compute_consume_dis = ${achievement.consume_dis},
                                consume_performance = project_price * ${achievement.consume_dis} / 100,
                                service_performance = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} / 100,
                                serv_div = (select proj_old_prise  from tn_customer_order_all t
                                    where t.id = tn.order_id ) * ${achievement.gift_dis} * ${achievement.service_dis} / 10000,
                                recv_div_new = project_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                                where  settle_status = 0 and order_state = 4
                                
                                and merchant_id not in (select merchant_id from tn_achievement_scale 
                               where status =1 and tp =1 and merchant_id is not null and small_category is null and 
                               big_category is null  and target_id is null and  mer_admin_id = ${achievement.mer_admin_id})

                               and project_id not in (select id from t_project_cfg where beaut_type  in (select big_category
                               from tn_achievement_scale where status =1 and tp =1 and big_category is not null 
                               and target_id is null  and small_category is null and mer_admin_id =
                               ${achievement.mer_admin_id} )) 
           
                               and project_id not in (select id from t_project_cfg  where body_part  in (select small_category from 
                               tn_achievement_scale where status =1 and tp = 1 and small_category is not null 
                               and target_id is null  and mer_admin_id = ${achievement.mer_admin_id}))
                                       
                               and project_id not in (select target_id from tn_achievement_scale where status = 1 and
                               tp = 1 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id})
                               and order_id in (select id from tn_customer_order_all  t where t.order_type = 4
                               t.mer_admin_id = ${achievement.mer_admin_id});`;
                               
                let smallsql = `begin  ${smallsql1245} ${smallsql3} ${smallsql6} end;`;
                await connection.execute(smallsql, []);

                return {code: 200, msg: '管理中心处理成功！'};
            }
            
            break;
        case 2: // 商品

            if (achievement.target_id) { // 存在具体商品
                console.log('商品-商品');
                if (achievement.merchant_id && achievement.mer_admin_id) { // 对具体店铺
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id

                    where m.merchant_id = ${achievement.merchant_id}

                    and  b.mer_admin_id = ${achievement.mer_admin_id}

                    and b.id = ${achievement.target_id}`;

                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set  
                             compute_sale_dis =  ${achievement.sale_dis},
                             compute_consume_dis = ${achievement.consume_dis},
                             consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                             sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                             recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                             consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                             where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                             on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                             = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }
                    return {code: 200, msg: '商品门店商品修改成功！'};
                } else {
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id

                    and  mer_admin_id = ${achievement.mer_admin_id}

                    and b.id = ${achievement.target_id}

                    and merchant_id not in (select merchant_id from tn_achievement_scale
                    where status = 1 and tp = 2
                    and big_category is null
                    and small_category is null
                    and merchant_id is not null 
                    and mer_admin_id = ${achievement.mer_admin_id})
                    
                    and b.main_type not in (select big_category from tn_achievement_scale 
                        where status =1 and tp =2 and big_category is not null and small_category is null and mer_admin_id 
                        is not null and merchant_id = ${achievement.merchant_id})
                    and b.small_type not in (select small_category from 
                        tn_achievement_scale where status =1 and tp = 2 and small_category is not null and mer_admin_id =
                        ${achievement.mer_admin_id} and merchant_id is not null )
                    and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                        tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                    and  merchant_id is not null)`;

                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set 
                        compute_sale_dis =  ${achievement.sale_dis},
                        compute_consume_dis = ${achievement.consume_dis},
                        consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                        sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                        recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                        consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                        where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                        on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                        = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }

                    return {code: 200, msg: '商品商品修改成功！'};
                }
            }
            if (achievement.small_category) { // 商品小类   没有增加排除的范围
                console.log('商品-小类');
                if (achievement.merchant_id && achievement.mer_admin_id) { // 对具体店铺
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id

                    where merchant_id = ${achievement.merchant_id}

                    and  mer_admin_id = ${achievement.mer_admin_id}

                    and b.small_type = ${achievement.small_category}
                    
                    and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                        tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                    and  merchant_id = ${achievement.merchant_id})`;

                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set 
                         compute_sale_dis =  ${achievement.sale_dis},
                         compute_consume_dis = ${achievement.consume_dis},
                         consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                         sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                         recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                         consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                         where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                         on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                         = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }
                    
                    return {code: 200, msg: '商品门店小类修改成功！'};
                } else {
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                                  inner join b2b_product_merchant_cfg m on b.id = m.product_id

                                  and  mer_admin_id = ${achievement.mer_admin_id}

                                  and b.small_type = ${achievement.small_category}

                                  and merchant_id not in (select merchant_id from tn_achievement_scale
                                  where status = 1 and tp = 2
                                  and big_category is null
                                  and small_category is null
                                  and merchant_id is not null 
                                  and mer_admin_id = ${achievement.mer_admin_id})
                        
                                  and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                                  tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id})`;
                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set  
                             compute_sale_dis =  ${achievement.sale_dis},
                             compute_consume_dis = ${achievement.consume_dis},
                             consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                             sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                             recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                             consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                             where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                             on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                             = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }
                    
                    return {code: 200, msg: '商品小类修改成功！'};
                }

            }
            if (achievement.big_category) {// 商品大类 =》除去具体商品 具体门店
                console.log('商品-大类');
                if (achievement.merchant_id) { // 存在商家
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id

                    where merchant_id = ${achievement.merchant_id}

                    and  mer_admin_id = ${achievement.mer_admin_id}

                    and b.main_type = ${achievement.big_category}
                    
                    and b.small_type not in (select small_category from 
                        tn_achievement_scale where status =1 and tp = 2 and small_category is not null and mer_admin_id =
                        ${achievement.mer_admin_id} and merchant_id =${achievement.merchant_id})
                    and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                        tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                    and  merchant_id = ${achievement.merchant_id})
                     `;
                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set  
                             compute_sale_dis =  ${achievement.sale_dis},
                             compute_consume_dis = ${achievement.consume_dis},
                             consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                             sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                             recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                             consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                             where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                             on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                             = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }
                    
                    return {code: 200, msg: '商品门店修改成功！'};


                } else { // 不存在商家
                    let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id

                    where mer_admin_id = ${achievement.mer_admin_id}

                    and b.main_type = ${achievement.big_category}

                    and merchant_id not in (select merchant_id from tn_achievement_scale
                        where status = 1 and tp = 2
                        and big_category is null
                        and small_category is null
                        and merchant_id is not null 
                        and mer_admin_id = ${achievement.mer_admin_id})
                        

                    and b.small_type not in (select small_category from 
                        tn_achievement_scale where status =1 and tp = 2 and small_category is not null and mer_admin_id =
                        ${achievement.mer_admin_id})
                    and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                        tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                    and  merchant_id = ${achievement.merchant_id})
                     `;
                    let sqlallresult = await connection.execute(sqlall, []);
                    let allproducts = sqlallresult.rows;

                    if (allproducts.length > 0){
                        let sql = 'begin ';
                        for (let i = 0; i < allproducts.length; i++) {
                            sql += `update b2b_order_detail set 
                             compute_sale_dis =  ${achievement.sale_dis},
                             compute_consume_dis = ${achievement.consume_dis},
                             consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                             sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                             recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                             consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                             where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                             on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                             = ${allproducts[i].merchant_id});`;
                        }
                        sql += 'end;';
                        await connection.execute(sql, []);
                    }

                    return {code: 200, msg: '商品门店修改成功！'};

                }
            }
            if (achievement.merchant_id) { // 门店
                console.log('商品-门店');
                // 所有的该门店的商品
                let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                inner join b2b_product_merchant_cfg m on b.id = m.product_id
                where merchant_id = ${achievement.merchant_id} 
                and b.main_type not in (select big_category from tn_achievement_scale 
                    where status =1 and tp =2 and big_category is not null and small_category is null and mer_admin_id =
                    ${achievement.mer_admin_id} and merchant_id = ${achievement.merchant_id})
                and b.small_type not in (select small_category from 
                    tn_achievement_scale where status =1 and tp = 2 and small_category is not null and mer_admin_id =
                    ${achievement.mer_admin_id} and merchant_id =${achievement.merchant_id})
                and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                    tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                and  merchant_id = ${achievement.merchant_id})`;

                let sqlallresult = await connection.execute(sqlall, []);
                let allproducts = sqlallresult.rows;

                if (allproducts.length > 0){
                    let sql = 'begin ';
                    for (let i = 0; i < allproducts.length; i++) {
                        sql += `update b2b_order_detail set 
                        compute_sale_dis =  ${achievement.sale_dis},
                        compute_consume_dis = ${achievement.consume_dis},
                        consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                        sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                        recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                        consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                        where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                        on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                        = ${allproducts[i].merchant_id});`;
                    }
                    sql += 'end;';
                    await connection.execute(sql, []);
                }

                return {code: 200, msg: '商品门店修改成功！'};

            }
            if (achievement.mer_admin_id) { // 管理中心 找出比其优先级高的商品 =》 订单 排除

                // 此处查找b2b_product mer_admin_id merchant_id 的存值
                // mer_admin_id 只有b2b_product 中有  merchant_id 只有 b2b_product_merchant_cfg 中有
                // 搜出 所有门店的受到影响的项目  不同门店的相同项目也需要列出
                let sqlall = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                inner join b2b_product_merchant_cfg m on b.id = m.product_id

                where mer_admin_id = ${achievement.mer_admin_id} 
                and  merchant_id not in (select merchant_id from tn_achievement_scale
                    where status = 1 and tp = 2
                    and big_category is null
                    and small_category is null
                    and merchant_id is not null 
                    and mer_admin_id = ${achievement.mer_admin_id}) 
                    
                    and b.main_type not in (select big_category from tn_achievement_scale 
                    where status =1 and tp =2 and big_category is not null and small_category is null and mer_admin_id =
                    ${achievement.mer_admin_id} and merchant_id is null) 

                    and b.small_type not in (select small_category from 
                    tn_achievement_scale where status =1 and tp = 2 and small_category is not null and mer_admin_id =
                    ${achievement.mer_admin_id} and merchant_id is null)
                    
                    and b.id not in (select target_id from tn_achievement_scale where status = 1 and
                    tp = 2 and target_id is not null and mer_admin_id = ${achievement.mer_admin_id}
                    and  merchant_id is null)`;


                let sqlallresult = await connection.execute(sqlall, []);
                let allproducts = sqlallresult.rows;
                // 去除了 门店，小类，大类,具体商品  设置了具体比例的商品


                // 找出 所有拥有门店大类 拥有门店小类的 门店id 然后 搜索

                // 找出拥有店铺 拥有 大类的 设置
                let alldelete = []; // 记录所有应该排除的 商品门店 因为其有更高的优先级

                // 应该排除的 店铺 --大类
                let sql1 = `select big_category "big_category" ,merchant_id "merchant_id" from tn_achievement_scale 
                where id  in  (select id  from tn_achievement_scale where status = 1 and tp =2 and
                big_category is not null and small_category is null and merchant_id is not null and
                mer_admin_id = ${achievement.mer_admin_id}) `;
                let sql1result = await connection.execute(sql1, []);
                // 假设 sql1result 是处理好的
                let promistall = [];
                for (let i = 0; i < sql1result.length; i++) {
                    let sql = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id  where b.main_type =
                    ${sql1result[i].big_category} and merchant_id = ${sql1result[i].merchant_id} and
                    mer_admin_id = ${achievement.mer_admin_id}`;
                    promistall.push(connection.execute(sql, []));
                }
                let bigproducts = await Promise.all(promistall);
                for (let i = 0; i < bigproducts.length; i++) {
                    if (bigproducts[i].rows.length !== 0) {
                        alldelete = alldelete.concat(bigproducts[i].rows);
                    }
                }

                // 找出拥有店铺 拥有 小类的设置
                let sql2 = `select small_category "small_category",merchant_id "merchant_id" from tn_achievement_scale
                where id in (select id  from tn_achievement_scale where status = 1 and tp =2 and
                small_category is not null  and merchant_id is not null and
                mer_admin_id = ${achievement.mer_admin_id})`;
                // 找出其拥有 影响的 店铺 项目
                let sql2result = await connection.execute(sql2, []);
                let promistall2 = [];
                for (let i = 0; i < sql2result.length; i++) {
                    let sql = `select b.id "id", m.merchant_id "merchant_id" from b2b_product b 
                    inner join b2b_product_merchant_cfg m on b.id = m.product_id  where b.small_type =
                    ${sql2result[i].small_category} and merchant_id = ${sql2result[i].merchant_id} and
                    mer_admin_id = ${achievement.mer_admin_id}`;
                    promistall2.push(connection.execute(sql, []));
                }
                let smallproducts = await Promise.all(promistall2);
                for (let i = 0; i < smallproducts.length; i++) {
                    if (smallproducts[i].rows.length !== 0) {
                        alldelete = alldelete.concat(smallproducts[i].rows);
                    }
                }


                // 找出拥有 店铺 拥有项目的设置 => 最终期望的结果

                let sql3 = `select target_id "target_id",merchant_id "merchant_id" from tn_achievement_scale 
                where id in  (select id  from tn_achievement_scale where status = 1 and tp =2 and
                target_id is not null  and merchant_id is not null and
                mer_admin_id = ${achievement.mer_admin_id})`;

                let sql3result = await connection.execute(sql3, []);
                if (sql3result.rows.length !== 0) {
                    alldelete = alldelete.concat(sql3result.rows);
                }

                // 去掉优先级比其高的
                for (let i = allproducts.length - 1; i >= 0; i--) {
                    for (let j = 0; j < alldelete.length; j++) {
                        if (allproducts[i].id === alldelete[j].target_id && allproducts[i].merchant_id === alldelete[j].merchant_id) {
                            allproducts.splice(i, 1);
                        }
                    }
                }
                // 此处处理 所有需要修改的数据 id商品id  merchant_id
                if (allproducts.length > 0){
                    let sql5 = 'begin ';
                    for (let i = 0; i < allproducts.length; i++) {
                        sql5 += `update b2b_order_detail set 
                       compute_sale_dis =  ${achievement.sale_dis},
                       compute_consume_dis = ${achievement.consume_dis},
                       consume_performance = count * buy_price * ${achievement.consume_dis} / 100,
                       sale_performance =   count * buy_price * ${achievement.sale_dis} / 100,
                       recv_div = count * buy_price * ${achievement.sale_dis} * staff_sale_dis / 10000,
                       consume_div = count * buy_price * ${achievement.consume_dis} * staff_consume_dis / 10000
                       where id in (select b.id from b2b_order_detail b  inner join b2b_product_merchant_cfg m
                       on b.product_id = m.product_id  where b.product_id = ${allproducts[i].id} and m.merchant_id
                       = ${allproducts[i].merchant_id});
                        `;
                    }
                    sql5 += 'end;';
                    await connection.execute(sql5, []);
                }

                return {code: 200, msg: '商品管理中心修改成功！'};

            }

            break;
        case 3: // 美丽金     target_id =>  merchant_id => mer_admin_id
            // tn_cash_pool_recharge          类型  2.美丽金充值 3会员卡 4次卡
            // 1.收款业绩提成比例 2.员工收款分红比例  3.是否已经结算
            // 美丽金只跟管理中心相关
            updatesql = `update tn_cash_pool_recharge set 
            compute_sale_dis = ${achievement.sale_dis}，
            sale_performance = pay_amount * ${achievement.sale_dis} / 100,
            recv_div = pay_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
            where merchant_id = ${achievement.mer_admin_id} and
            settle_status = 0  and trans_type = 2`;
            await connection.execute(updatesql, []);
            return {code: 200, msg: '美丽金更新成功！'};
            break;
        case 4: // 会员卡      target_id => merchant_id =>  mer_admin_id
            // tn_cash_pool_recharge          类型  2.美丽金充值 3会员卡 4次卡
            // 会员卡只跟管理中心相关
            updatesql = `update tn_cash_pool_recharge set 
             compute_sale_dis = ${achievement.sale_dis}，
             sale_performance = pay_amount * ${achievement.sale_dis} /100 ,
             recv_div = pay_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
             where merchant_id = ${achievement.mer_admin_id} and
             settle_status = 0  and trans_type in (3)`;
            // settle_status  1已经算 0未结算
            // 1 已经支付 0 未支付
            // compute_sale_dis 收款业绩提成比例
            // staff_sale_dis   员工收款分红比例  =>这里应该在修改员工提成比例时修改
            // sale_performance 收款业绩 =》 等于实际收款* 收款业绩提成比例
            await connection.execute(updatesql, []);
            return {code: 200, msg: '会员卡更新成功！'};
        case 5: // 疗程卡      target_id => merchant_id =>  mer_admin_id
            // tn_cash_pool_recharge          类型  2.美丽金充值 3会员卡 4次卡
            // 疗程卡只跟管理中心相关
            updatesql = `update tn_cash_pool_recharge set 
             compute_sale_dis = ${achievement.sale_dis}，
             sale_performance = pay_amount * ${achievement.sale_dis} /100 ,
             recv_div = pay_amount * ${achievement.sale_dis} * staff_sale_dis / 10000
             where merchant_id = ${achievement.mer_admin_id} and
             settle_status = 0  and trans_type in (4)`;
            // settle_status  1已经算 0未结算
            // 1 已经支付 0 未支付
            // compute_sale_dis 收款业绩提成比例
            // staff_sale_dis   员工收款分红比例  =>这里应该在修改员工提成比例时修改
            // sale_performance 收款业绩 =》 等于实际收款* 收款业绩提成比例
            await connection.execute(updatesql, []);
            return {code: 200, msg: '疗程卡更新成功！'};
        default: // 类型错误

            return {code: 201, msg: '绩效设置类型错误！'};
    }
}

/**
 * tn_job_bouns 职位绩效提成比例
 * job_id 职位id
 * status  1正常 -1 删除
 * month_bonus_dis 月度 销售提点        =>根据类型
 * season_bonus_dis 季度 销售提点
 * year_bonus_dis 年度 销售提点
 *
 * bonus_type 1:销售分红2:服务分红3:培养下属分红4:管店分红5:投资分红6:赠送分红7:绩效分红8:推荐分红9:消耗分红10:后勤奖金
 * bonus_type 注释类型有错误
 * month_consume_dis 月度消耗提点
 * season_consume_dis 季度消耗提点
 * year_consume_dis  年度消耗提点
 *
 * merchant_id
 * mer_admin_id
 *
 */

/**
 * 销售分红更新相关订单
 * 门店=》岗位=》人=》订单
 * 参数：id 职位绩效提成比例id
 * bonus_type  1 销售分红
 **/
async function saleBonusDisUpdate(id, connection) {
    if (!utility.isNumber(id)) {
        return {code: 201, msg: 'id传参错误！'};
    }
    if (!connection) {
        return {code: 201, msg: '请传入数据库连接！'};
    }
    const bonussql = `select job_id "job_id",
                   bonus_type "bonus_type",
                   month_bonus_dis "month_bonus_dis",
                   merchant_id "merchant_id",
                   mer_admin_id "mer_admin_id"
                   from tn_job_bouns where id = ${id}`;
    const result = await connection.execute(bonussql, []);
    if (result.rows.length === 0) {
        return {code: 201, msg: '没有查询到数据！'};
    }
    const bonus = result.rows[0];
    if (bonus.bonus_type !== 1) {
        return {code: 201, msg: '绩效表单类型错误！'};
    }
    // 校验完毕 开始查找影响的人数
    // 排除具体店铺的影响
    // tn_job_bouns.job_id => tn_job.id => m_user (user_type  1客户 2商家 3平台)

    let userssql = '';
    if (bonus.merchant_id) { // 具体店铺 不用考虑影响
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and status = 1 and user_type = 2
                   and merchant_id = ${bonus.merchant_id}
                   and mer_admin_id = ${bonus.mer_admin_id}`;
    } else {        // 需要考虑具体影响
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and job_id not in (select job_id "job_id"
                   from tn_job_bouns where 
                   merchant_id is not null and 
                   status = 1 and bonus_type =1)
                   and status = 1 and user_type = 2
                   and mer_admin_id = ${bonus.mer_admin_id}`;
    }
    let results = await connection.execute(userssql, []);
    if (results.rows.length === 0) {
        return {code: 200, msg: '暂时无影响的客户！'};
    }
    const users = results.rows;
    const usersid = [];
    for (let i = 0; i < users.length; i++) {
        usersid.push(users[i].id);
    }
    // 查询用户影响的订单 SALE_PERFORMANCE
    // tn_customer_order_all.guwen_id   销售员id
    // 什么样的订单类型 计算 销售分红
    let upordersql = `update tn_customer_order_all set 
                       staff_sale_dis = ${bonus.month_bonus_dis},
                       recv_div =  SALE_PERFORMANCE * ${bonus.month_bonus_dis}  / 100
                       where settle_status = 0
                       and guwen_id in (${usersid.join()}) `;
    let upmljsql = `update  tn_cash_pool_recharge set
                    staff_sale_dis = ${bonus.month_bonus_dis},
                    recv_div = SALE_PERFORMANCE * ${bonus.month_bonus_dis}  / 100
                    where settle_status = 0
                    and seller_id in (${usersid.join()}) `;
    // let upb2bsql =  `update b2b_order set
    //                  staff_sale_dis = ${bonus.month_bonus_dis},
    //                  recv_div = SALE_PERFORMANCE * ${bonus.month_bonus_dis}
    //                  where settle_status =2
    //                  and recv_div_id in (${usersid.join()}) `;
    let up2bbdet = `update b2b_order_detail set 
                    staff_sale_dis = ${bonus.month_bonus_dis},
                    recv_div = SALE_PERFORMANCE * ${bonus.month_bonus_dis} / 100
                    where  id in(select bd.id from b2b_order_detail bd left join 
                    b2b_order b on bd.order_id = b.id where b.recv_div_id
                    in (${usersid.join()}) and b.settle_status = 0 ) `;

    await Promise.all([connection.execute(upordersql, []), connection.execute(upmljsql, []), connection.execute(up2bbdet, [])]);

    return {code: 200, msg: '更新成功！'};

}
/**
 * 消耗分红更新
 * 门店=》岗位=》人=》订单
 * 消耗分红仅关联到小表 与大表无关
 * compuse 100% staff_sale_dis 1% sale_performance 业绩
 **/
async function consumeBonusDisUpdate(id, connection) {
    if (!utility.isNumber(id)) {
        return {code: 201, msg: 'id传参错误！'};
    }
    if (!connection) {
        return {code: 201, msg: '请传入数据库连接！'};
    }
    const bonussql = `select job_id "job_id",
                   bonus_type "bonus_type",
                   month_bonus_dis "month_bonus_dis",
                   merchant_id "merchant_id",
                   mer_admin_id "mer_admin_id"
                   from tn_job_bouns where id = ${id}`;
    const result = await connection.execute(bonussql, []);
    if (result.rows.length === 0) {
        return {code: 201, msg: '没有查询到数据！'};
    }
    const bonus = result.rows[0];
    if (bonus.bonus_type !== 5) {
        return {code: 201, msg: '消耗表单类型错误！'};
    }
    // 校验完毕 开始查找影响的人数
    // tn_job_bouns.job_id => tn_job.id => m_user (user_type  1客户 2商家 3平台)
    let userssql = '';
    if (bonus.merchant_id) {
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and status = 1 and user_type = 2
                   and merchant_id = ${bonus.merchant_id}
                   and mer_admin_id = ${bonus.mer_admin_id}`;
    } else {
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and status = 1 and user_type = 2
                   and mer_admin_id = ${bonus.mer_admin_id}
                   and job_id not in (select job_id "job_id"
                   from tn_job_bouns where 
                   merchant_id is not null and 
                   status = 1 and bonus_type =5)
                   `;
    }
    let results = await connection.execute(userssql, []);
    if (results.rows.length === 0) {
        return {code: 200, msg: '暂时无影响的客户！'};
    }
    const users = results.rows;
    const usersid = [];
    for (let i = 0; i < users.length; i++) {
        usersid.push(users[i].id);
    }
    // 查询用户影响的订单
    // tn_customer_order.recver_id_new    接待员id

    let upordersql = `update tn_customer_order set 
                       staff_consume_dis = ${bonus.month_bonus_dis},
                        recv_div_new = ${bonus.month_bonus_dis} *consume_performance / 100
                       where  order_state = 4
                       and settle_status = 0
                       and recver_id_new in (${usersid.join()}) `;
    // 缺少商品消耗分红
    let upb2bsql = `update b2b_order_detail set 
                       staff_consume_dis = ${bonus.month_bonus_dis},
                       consume_div  = ${bonus.month_bonus_dis} * consume_performance / 100
                       where  order_id in (select id from b2b_order where
                       recv_div_id in (${usersid.join()}))`;

    await Promise.all([connection.execute(upordersql, []), connection.execute(upb2bsql, [])]);

    return {code: 200, msg: '更新成功！'};

}
/**
 * 服务分红更新 废弃，其写在 tn_achievement_scale 中
 * 门店=》岗位=》人=》订单
 *  bonus_type 1:销售分红2:服务分红3:培养下属分红4:管店分红5:投资分红6:赠送分红7:绩效分红8:推荐分红9:消耗分红10:后勤奖金
 **/
async function serviceBonusDisUpdate(id, connection) {
    if (!utility.isNumber(id)) {
        return {code: 201, msg: 'id传参错误！'};
    }
    if (!connection) {
        return {code: 201, msg: '请传入数据库连接！'};
    }
    const bonussql = `select job_id "job_id",
                   bonus_type "bonus_type",
                   month_bonus_dis "month_bonus_dis",
                   merchant_id "merchant_id",
                   mer_admin_id "mer_admin_id"
                   from tn_job_bouns where id = ${id}`;
    const result = await connection.execute(bonussql, []);
    if (result.rows.length === 0) {
        return {code: 201, msg: '没有查询到数据！'};
    }
    const bonus = result.rows[0];
    if (bonus.bonus_type !== 2) {
        return {code: 201, msg: '消耗表单类型错误！'};
    }
    // 校验完毕 开始查找影响的人数
    // tn_job_bouns.job_id => tn_job.id => m_user (user_type  1客户 2商家 3平台)
    let userssql = '';
    if (bonus.merchant_id) {
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and status = 1 and user_type = 2
                   and merchant_id = ${bonus.merchant_id}
                   and mer_admin_id = ${bonus.mer_admin_id}`;
    } else {
        userssql = `select id "id" from m_user where job_id = ${bonus.job_id}
                   and status = 1 and user_type = 2
                   and mer_admin_id = ${bonus.mer_admin_id}
                   and job_id not in (select job_id "job_id"
                   from tn_job_bouns where 
                   merchant_id is not null and 
                   status = 1 and bonus_type =2)`;
    }
    let results = await connection.execute(userssql, []);
    if (results.rows.length === 0) {
        return {code: 200, msg: '暂时无影响的客户！'};
    }
    const users = results.rows;
    const usersid = [];
    for (let i = 0; i < users.length; i++) {
        usersid.push(users[i].id);
    }
    // 查询用户影响的订单
    // tn_customer_order.technician_id    服务员id

    let upordersql = `update tn_customer_order set 
                       staff_service_dis = ${bonus.month_bonus_dis},
                       serv_div = ${bonus.month_bonus_dis} * service_performance / 100
                       where order_state = 4
                       and settle_status = 0
                       and technician_id in (${usersid.join()}) `;
    await connection.execute(upordersql, []);
    return {code: 200, msg: '更新成功！'};
}


// 刷新 订单类型=》（赠品，活动，普通） 订单原价
// 赠品  根据大表的类型更新
// 活动  根据价格判断
// 普通  根据价格判断

module.exports = paymentTools;
